{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QKBCLNZVAv_j"
   },
   "source": [
    "## Install the Spanner Python API\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7uhDuh2B3-CX"
   },
   "outputs": [],
   "source": [
    "! pip install --upgrade google-cloud-spanner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f-wsCeqBAyWl"
   },
   "source": [
    "## Set the following variables \n",
    "\n",
    "You must update the Project ID variable and the Region ID variable. You can change the other variables if you like. Please leave the outside single quotes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lJIrqAZz4CzU"
   },
   "outputs": [],
   "source": [
    "project_id = 'your-project-id-here'\n",
    "region_id = 'your-region-here'\n",
    "instance_id = 'spanner-postgres'\n",
    "\n",
    "processing_units = 100\n",
    "database_id = 'pets-postgres'\n",
    "\n",
    "OPERATION_TIMEOUT_SECONDS = 240\n",
    "\n",
    "!gcloud services enable spanner.googleapis.com \n",
    "print(\"Spanner Enabled\")\n",
    "\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iziApd7nA5F1"
   },
   "source": [
    "## Create a Spanner instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "fUJGt4or4DgG"
   },
   "outputs": [],
   "source": [
    "import time\n",
    "from google.cloud import spanner\n",
    "\n",
    "def create_instance_with_processing_units(instance_id, processing_units):\n",
    "    \"\"\"Creates an instance.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "    config_name = f\"{spanner_client.project_name}/instanceConfigs/regional-{region_id}\"\n",
    "\n",
    "    instance = spanner_client.instance(\n",
    "        instance_id,\n",
    "        configuration_name=config_name,\n",
    "        display_name=instance_id,\n",
    "        processing_units=processing_units,\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = instance.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\n",
    "        \"Created instance {} with {} processing units\".format(\n",
    "            instance_id, instance.processing_units\n",
    "        )\n",
    "    )\n",
    "\n",
    "# Call the function\n",
    "create_instance_with_processing_units(instance_id, processing_units)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ghw0o_qxBFIe"
   },
   "source": [
    "## Create the Pets database\n",
    "\n",
    "Note: The parameter database_dialect-2 makes this a PostgreSQL database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hpgkaDz24Osx"
   },
   "outputs": [],
   "source": [
    "def create_database(instance_id, database_id):\n",
    "    \"\"\"Creates a database and tables for sample data.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "\n",
    "    database = instance.database(\n",
    "        database_id, database_dialect=2\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = database.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Created database {} on instance {}\".format(database_id, instance_id))\n",
    "\n",
    "# Call the function\n",
    "create_database(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VRPYRaVaBHwp"
   },
   "source": [
    "## The following function runs DDL statements to build the database schema\n",
    "\n",
    "The parameter to the update_ddl() function is an array. You can run one or more DDL commands. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6JY0xghs6SaR"
   },
   "outputs": [],
   "source": [
    "def run_ddl_statement(instance_id, database_id, ddl):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    operation = database.update_ddl(\n",
    "        [ddl]\n",
    "    )\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Ran statement: {}\".format(ddl))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IDA3A4VYBSYp"
   },
   "source": [
    "## Create the Owners table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "FIf7iqy16_2r"
   },
   "outputs": [],
   "source": [
    "ddl = \"\"\"CREATE TABLE Owners (\n",
    "                  OwnerID VARCHAR(36) PRIMARY KEY,\n",
    "                  OwnerName VARCHAR(1024) NOT NULL\n",
    "               )\"\"\"\n",
    "\n",
    "run_ddl_statement(instance_id, database_id, ddl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5jWaHTkBBW2z"
   },
   "source": [
    "## Create the Pets table\n",
    "\n",
    "Note: the Pets table is interleaved with the Owners table. This means each pet is stored with its owner. The primary key of the Pets table uses both OwnerID and PetID. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "afnOhO3v8LMQ"
   },
   "outputs": [],
   "source": [
    "ddl = \"\"\"CREATE TABLE Pets (\n",
    "                  OwnerID VARCHAR(36) NOT NULL, \n",
    "                  PetID VARCHAR(36) NOT NULL,     \n",
    "                  PetType VARCHAR(1024) NOT NULL,\n",
    "                  PetName VARCHAR(1024) NOT NULL,\n",
    "                  Breed VARCHAR(1024) NOT NULL,\n",
    "                  PRIMARY KEY (OwnerID, PetID)\n",
    "              ) \n",
    "              INTERLEAVE IN PARENT Owners ON DELETE CASCADE\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "run_ddl_statement(instance_id, database_id, ddl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hWEAc578B1Ij"
   },
   "source": [
    "## Display the database schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "1J_oDtTK9KVZ"
   },
   "outputs": [],
   "source": [
    "def get_database_ddl(instance_id, database_id):\n",
    "    \"\"\"Gets the database DDL statements.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    ddl = spanner_client.database_admin_api.get_database_ddl(database=database.name)\n",
    "\n",
    "    print(\"Retrieved database DDL for {}\".format(database_id))\n",
    "\n",
    "    for statement in ddl.statements:\n",
    "        print(statement)\n",
    "\n",
    "\n",
    "get_database_ddl(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GZRGAvhjB8ET"
   },
   "source": [
    "## Add some test records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LO-z1x629VKF"
   },
   "outputs": [],
   "source": [
    "import uuid\n",
    "\n",
    "def insert_data(instance_id, database_id):\n",
    "    doug_id = str(uuid.uuid4())\n",
    "    john_id = str(uuid.uuid4())\n",
    "    sue_id = str(uuid.uuid4())\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.batch() as batch:\n",
    "        batch.insert(\n",
    "            table=\"Owners\",\n",
    "            columns=(\"OwnerID\", \"OwnerName\"),\n",
    "            values=[\n",
    "                (doug_id, u\"Doug\"),\n",
    "                (john_id, u\"John\"),\n",
    "                (sue_id, u\"Sue\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "        batch.insert(\n",
    "            table=\"Pets\",\n",
    "            columns=(\"PetID\", \"OwnerID\", \"PetType\", \"PetName\", \"Breed\"),\n",
    "            values=[\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Noir\", u\"Schnoodle\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Bree\", u\"Mutt\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Cat\", u\"Tom\", u\"Alley\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Duke\", u\"GoldenDoodle\"),\\\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Sparky\", u\"Poodle\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Cuff\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Link\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), sue_id, u\"Cat\", u\"Cleo\", u\"Domestic\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "    print(\"Inserted data.\")\n",
    "\n",
    "\n",
    "insert_data(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kXeryKjV_aqU"
   },
   "source": [
    "## Go to the Google Cloud Console and verify that the Spanner instance, database, tables, and records were all created. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "68K_pvCuCUUq"
   },
   "source": [
    "## Runs the query passed as an argument\n",
    "\n",
    "This function runs any SELECT query passed as an argument. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cbNHZ51G-GAq"
   },
   "outputs": [],
   "source": [
    "def run_query(sql):\n",
    "  # Instantiate a client.\n",
    "  spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "  # Get a Cloud Spanner instance by ID.\n",
    "  instance = spanner_client.instance(instance_id)\n",
    "\n",
    "  # Get a Cloud Spanner database by ID.\n",
    "  database = instance.database(database_id)\n",
    "\n",
    "  # Execute a simple SQL statement.\n",
    "  with database.snapshot() as snapshot:\n",
    "      results = snapshot.execute_sql(sql)\n",
    "      for row in results:\n",
    "        print(row)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "k3QOelQ6CeKG"
   },
   "source": [
    "## Query with a join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Ofdj6ok2-OEj"
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT Owners.OwnerID, OwnerName, PetName, PetType, Breed \n",
    "         FROM Owners \n",
    "         JOIN Pets ON Owners.OwnerID = Pets.OwnerID;\"\"\"\n",
    "\n",
    "run_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-mB52ZWOC3rL"
   },
   "source": [
    "## Delete all of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cwpex85KC6N7"
   },
   "outputs": [],
   "source": [
    "def delete_data_with_dml(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    def delete_owners(transaction):\n",
    "        \n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM Owners WHERE true = true\"\n",
    "        )\n",
    "\n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "\n",
    "    database.run_in_transaction(delete_owners)\n",
    "\n",
    "# Call the function\n",
    "delete_data_with_dml(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HfjCtt0CDmgm"
   },
   "source": [
    "## Delete the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LZjVAHLoDszL"
   },
   "outputs": [],
   "source": [
    "def delete_database(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    \n",
    "    database.drop()\n",
    "\n",
    "    print(\"{} database dropped\".format(database_id))\n",
    "\n",
    "\n",
    "# Call the function\n",
    "delete_database(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QawIviJTD1lb"
   },
   "source": [
    "## Delete the instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-98p13_kD4-X"
   },
   "outputs": [],
   "source": [
    "def delete_spanner_instance(instance_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    instance.delete()\n",
    "\n",
    "    print(\"{} instance deleted\".format(instance_id))\n",
    "\n",
    "# Call the function\n",
    "delete_spanner_instance(instance_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GS9LUsmhAAL5"
   },
   "source": [
    "## Go to the Console and verify that the Spanner instance was deleted. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyP4j/aAs/M//3GybY2BdAT1",
   "collapsed_sections": [],
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3.8.2 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
